package com.example.demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.demo.entity.PmEmployee;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;

@Mapper
@Repository
public interface PmEmployeeMapper extends BaseMapper<PmEmployee> {

    @Select(("SELECT * FROM pm_employee WHERE id = #{id}"))
    PmEmployee getById(Long id);

    @Select("select degree, COUNT(degree) as count from pm_employee " +
            "where is_regular is true AND is_leave is false AND onboarding_time <= #{date} AND company like #{company} AND is_deleted is false " +
            "group by degree")
    List<HashMap<String, Long>> countByDegree(String date, String company);

    @Select("select age_temp, count(*) as count from ( select age," +
            "case " +
            "when age <= 30 then '30-' " +
            "when age >= 31 and age <= 40 then '31-40' " +
            "when age >= 41 and age <= 50 then '41-50' " +
            "when age >= 51 then '51+' " +
            "end as age_temp " +
            "from pm_employee " +
            "where is_regular is true AND onboarding_time <= #{date} AND is_deleted is false AND is_leave is false AND company like #{company}) " +
            "pm_employee group by age_temp")
    List<HashMap<String, Long>> countByAge(String date, String company);

    @Select("select IFNULL(SUM(age),0) as age from pm_employee " +
            "where is_regular is true AND is_leave is false AND is_deleted is false AND onboarding_time <= #{date} AND company like #{company}")
    HashMap<String, Double> ageSum(String date, String company);

    @Select("select employment_type, COUNT(employment_type) as count from pm_employee " +
            "where onboarding_time <= #{date} AND is_leave is false AND is_deleted is false AND company like #{company} " +
            "group by employment_type")
    List<HashMap<String, Long>> countByEmployeeType(String date, String company);

    @Select("select political_status, COUNT(political_status) as count from pm_employee " +
            "where is_regular is true AND is_leave is false AND is_deleted is false AND onboarding_time <= #{date} AND company like #{company} " +
            "group by political_status")
    List<HashMap<String, Long>> countByPoliticalStatus(String date, String company);

    @Select("select position_status, COUNT(position_status) as count from pm_employee " +
            "where onboarding_time <= #{date} AND is_leave is false AND is_deleted is false AND company like #{company} " +
            "group by position_status")
    List<HashMap<String, Long>> countByPositionStatus(String date, String company);

    @Select("select sex, COUNT(sex) as count from pm_employee " +
            "where is_regular is true AND is_leave is false AND is_deleted is false AND onboarding_time <= #{date} AND company like #{company} " +
            "group by sex")
    List<HashMap<String, Long>> countBySex(String date, String company);

    @Select("select professional_title, COUNT(professional_title) as count from pm_employee " +
            "where is_regular is true AND is_leave is false AND is_deleted is false AND onboarding_time <= #{date} AND company like #{company} " +
            "group by professional_title")
    List<HashMap<String, Long>> countByProfessionalTitle(String date, String company);

    @Select("select position_status, COUNT(position_status) as count from pm_employee " +
            "where onboarding_time >= #{startDate} AND onboarding_time <= #{endDate} AND is_deleted is false AND company like #{company} " +
            "group by position_status")
    List<HashMap<String, Long>> newThisMonthPositionStatus(String startDate, String endDate, String company);

    @Select("select recruitment_source, COUNT(recruitment_source) as count from pm_employee " +
            "where onboarding_time >= #{startDate} AND onboarding_time <= #{endDate} AND is_regular is true AND is_deleted is false AND company like #{company} " +
            "group by recruitment_source")
    List<HashMap<String, Long>> newThisMonthRecruitmentSource(String startDate, String endDate, String company);

    @Select("select count(*) as count from pm_employee " +
            "where onboarding_time <= #{endDate} AND onboarding_time >= #{startDate} AND is_deleted is false AND company like #{company}")
    HashMap<String, Integer> newSum(String startDate, String endDate, String company);

    @Select("select count(*) as count from pm_employee " +
            "where onboarding_time <= #{endDate} AND onboarding_time >= #{startDate} AND is_regular is true AND is_deleted is false AND company like #{company}")
    HashMap<String, Long> newSumRegular(String startDate, String endDate, String company);

    @Select("select count(*) as count from pm_employee " +
            "where onboarding_time <= #{endDate} AND onboarding_time >= #{startDate} " +
            "AND is_deleted is false AND is_regular is true " +
            "AND sex = '女' AND company like #{company}")
    HashMap<String, Integer> newSumFemale(String startDate, String endDate, String company);

    @Select("select position_status, COUNT(position_status) as count from pm_employee " +
            "where terminate_time >= #{startDate} AND terminate_time <= #{endDate} AND is_leave is true AND is_deleted is false AND company like #{company} " +
            "group by position_status")
    List<HashMap<String, Long>> leaveThisMonthPositionStatus(String startDate, String endDate, String company);

    @Select("select count(*) as count from pm_employee " +
            "where terminate_time <= #{endDate} AND terminate_time >= #{startDate} " +
            "AND is_leave is true AND is_regular is true AND is_deleted is false AND company like #{company}")
    HashMap<String, Long> leaveSum(String startDate, String endDate, String company);

    @Select("select leave_reason, COUNT(leave_reason) as count from pm_employee " +
            "where terminate_time >= #{startDate} AND terminate_time <= #{endDate} " +
            "AND is_deleted is false AND is_regular is true " +
            "AND is_leave is true AND company like #{company} " +
            "group by leave_reason")
    List<HashMap<String, Long>> leaveThisMonthReason(String startDate, String endDate, String company);

    @Select("select count(*) as count from pm_employee " +
            "where terminate_time <= #{endDate} AND terminate_time >= #{startDate} " +
            "AND is_deleted is false AND is_regular is true " +
            "AND sex = '女' AND company like #{company}")
    HashMap<String, Integer> leaveSumFemale(String startDate, String endDate, String company);

    @Select("select employment_type, COUNT(employment_type) as count from pm_employee " +
            "where onboarding_time >= #{startDate} AND onboarding_time <= #{endDate} AND is_deleted is false AND company like #{company} " +
            "group by employment_type")
    List<HashMap<String, Long>> newThisMonthEmploymentType(String startDate, String endDate, String company);

    @Select("select employment_type, COUNT(employment_type) as count from pm_employee " +
            "where terminate_time >= #{startDate} AND terminate_time <= #{endDate} AND is_deleted is false AND company like #{company} " +
            "group by employment_type")
    List<HashMap<String, Long>> leaveThisMonthEmploymentType(String startDate, String endDate, String company);

    @Select("select count(*) as count from pm_employee " +
            "where terminate_time <= #{endDate} AND terminate_time >= #{startDate} AND (position_status like '%中层%' OR position_status like '%经营层%') " +
            "AND is_leave is true AND is_deleted is false AND is_regular is true AND company like #{company}")
    HashMap<String, Long> leaveManager(String startDate, String endDate, String company);
}